package com.vilynn.web.excel;

import com.alibaba.fastjson.JSON;
import com.juqitech.service.utils.CommonUtil;
import com.vilynn.web.vo.MiniAppDataVo;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * ExcelReader
 *
 * @author Weilin Wang
 * @since 2020/3/31
 */
public class ExcelReader {

    public static Map<Integer, List<MiniAppDataVo>> parse(String filePath) throws IOException {

        Map<Integer, List<MiniAppDataVo>> map = new HashMap<>();

        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(filePath));

        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {

            XSSFSheet sheet = workbook.getSheetAt(i);

            map.put(i, parse(sheet));
        }

        return map;
    }

    private static List<MiniAppDataVo> parse(XSSFSheet sheet) {

        List<MiniAppDataVo> list = new ArrayList<>();

        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            XSSFRow row = sheet.getRow(i);
            if("miniprogram".equals(sheet.getSheetName())){
                list.add(newMiniAppDataVo1(row));
            } else {
                list.add(newMiniAppDataVo(row, 1, 3));
            }
        }

        return list;
    }


    private static MiniAppDataVo newMiniAppDataVo1(XSSFRow row) {
        MiniAppDataVo dataVo = new MiniAppDataVo();
//        dataVo.setId(row.getCell(1).getStringCellValue());
        dataVo.setMiniappType(row.getCell(2) == null ? "" : row.getCell(2).getStringCellValue());
        dataVo.setAppId(row.getCell(3).getStringCellValue());
        dataVo.setSecret(row.getCell(4).getStringCellValue());
        dataVo.setAppName(row.getCell(5).getStringCellValue());
        dataVo.setAccountName(row.getCell(6).getStringCellValue());
        dataVo.setIs_enable(row.getCell(7).getRawValue());
        dataVo.setIs_deleted(row.getCell(8).getRawValue());
        dataVo.setIs_refresh_token(row.getCell(11).getRawValue());
        return dataVo;
    }

    private static MiniAppDataVo newMiniAppDataVo(XSSFRow row, int i, int j) {

        MiniAppDataVo dataVo = new MiniAppDataVo();
        dataVo.setAppId(row.getCell(i).getStringCellValue());
        dataVo.setSecret(row.getCell(j).getStringCellValue());
        return dataVo;
    }

    public static List<MiniAppDataVo> compare(List<MiniAppDataVo> list1, List<MiniAppDataVo> list2){
        return list1.stream().filter(vo1 -> {
            for (MiniAppDataVo vo2 : list2) {
//                if(vo2.getAppId().equals(vo1.getAppId()) && vo2.getSecret().equals(vo1.getSecret())){
                if(vo2.getAppId().equals(vo1.getAppId())){
                    return false;
                }
            }
            return true;
        }).collect(Collectors.toList());
    }

    public static void main(String[] args) throws IOException {
        Map<Integer, List<MiniAppDataVo>> map = parse("e:/miniprogram.xlsx");
        List<MiniAppDataVo> result = compare(map.get(0), map.get(1));
        System.out.println(JSON.toJSONString(result));

        String sql = "INSERT INTO `miniapp_center`.`miniapp`(`id`, `app_id`, `app_name`, `app_secret`, " +
                "`miniapp_type`, `is_refresh_token`, `dd_corp_id`, `enabled`, `is_deleted`, `create_time`, `update_time`) VALUES ('%s', '%s', '%s', '%s', '%s', %s, '%s', %s, %s, '2020-04-01 00:00:00', '2020-04-01 00:00:00');";
        result.forEach(res -> {
            System.out.println(String.format(sql,
                    CommonUtil.generateOID(),
                    res.getAppId(),
                    res.getAppName(),
                    res.getSecret(),
                    StringUtils.isBlank(res.getMiniappType()) ? "WEIXIN_MINI" : res.getMiniappType(),
                    res.getIs_refresh_token(),
                    "",
                    res.getIs_enable(),
                    res.getIs_deleted()
                    ));
        });
    }

}
